

# one()：只获取唯一一条记录, 找不到记录或找到多条记录时会报错
res_one = db.session.query(UserInfo).filter(UserInfo.id == 141).one()
print('res_one: ', res_one)

# one_or_none()：只获取唯一一条记录, 找不到记录或找到多条记录时会报错
res_one_or_none = db.session.query(UserInfo).filter(UserInfo.id == 141).one_or_none()
print('res_one_or_none: ', res_one_or_none)

# scalar()：同one, 没有结果时返回None
res_scalar = db.session.query(UserInfo).filter(UserInfo.id == 141).scalar()
print('res_scalar: ', res_scalar)

# count()：返回记录条数
res_count = db.session.query(UserInfo).filter(UserInfo.id < 141).count()
print('res_count: ', res_count)

# limit()：限制返回条数
res_limit = db.session.query(UserInfo).filter(UserInfo.id < 141).limit(10).all()
print('res_limit: ', res_limit)

# distinct()：过滤掉多余的重复记录只保留一条
res_distinct = db.session.query(UserInfo.department).distinct(UserInfo.department).count()
print('res_distinct: ', res_distinct)

# order_by()：查询结果排序
res_order_by_desc = db.session.query(UserInfo).order_by(UserInfo.id.desc()).all()  # 倒序
print('res_order_by_desc: ', res_order_by_desc)
res_order_by_desc_1 = db.session.query(UserInfo).order_by(desc(UserInfo.id)).all()  # 使用desc
print('res_order_by_desc_1: ', res_order_by_desc_1)
res_order_by_asc = db.session.query(UserInfo).order_by(UserInfo.id.asc()).all()  # 正序
print('res_order_by_asc: ', res_order_by_asc)
res_order_by_asc_1 = db.session.query(UserInfo).order_by(asc(UserInfo.id)).all()  # 使用asc
print('res_order_by_asc_1: ', res_order_by_asc_1)







# exist()
res_exists = db.session.query(exists().where(UserInfo.id < 100)).scalar()  # 查看记录是否存在
print('res_exists: ', res_exists)
# 与
res_and = db.session.query(UserInfo).filter(UserInfo.id < 2000, UserInfo.department == '4d9f58e75204441c9744aa95e2b7fa88').all()  # all()返回所有记录
print('res_and: ', res_and)
res_and_ = db.session.query(UserInfo).filter(and_(UserInfo.id > 2000, UserInfo.department == '4d9f58e75204441c9744aa95e2b7fa88')).all()  # and_实现And查询
print('res_and_: ', res_and_)
print('to_dict: ', [res.to_dict() for res in res_and_])  # 一行代码实现: 返回数据格式化为易读格式

# 或
res_or = db.session.query(UserInfo).filter(or_(UserInfo.id == 141, UserInfo.name == '李四')).all()
print('res_or: ', res_or)

# AND与OR并存
res_and_or = db.session.query(UserInfo).filter(or_(UserInfo.id < 1500, UserInfo.id > 2000), UserInfo.department == '4d9f58e75204441c9744aa95e2b7fa88').all()
print('res_and_or: ', res_and_or)
res_or_and = db.session.query(UserInfo).filter(or_(UserInfo.department == '4d9f58e75204441c9744aa95e2b7fa88', and_(UserInfo.id < 200, UserInfo.name.like('李%')))).all()
print('res_or_and', res_or_and)

# in
res_in = db.session.query(UserInfo).filter(UserInfo.id.in_([10, 141, 1688])).all()
print('res_in: ', res_in)

# like
res_like = db.session.query(UserInfo).filter(UserInfo.name.like('李%')).all()
print('res_like: ', res_like)

# contains
res_contains = db.session.query(UserInfo).filter(UserInfo.name.contains('李')).all()
print('res_contains: ', res_contains)

# NOT
res_not = db.session.query(UserInfo).filter(~UserInfo.id.in_([11, 12])).all()
print('res_not: ', res_not)

# 查询指定列
res_custom_column = db.session.query(UserInfo.id, UserInfo.name, UserInfo.mobile).filter(UserInfo.id < 15).all()
print('res_custom_column: ', res_custom_column)

# 使用列表给filter方法传参
conditions = list()
conditions.append(UserInfo.name.like('李%'))
conditions.append(UserInfo.id < 200)
res_conditions = db.session.query(UserInfo).filter(*conditions).all()
print('res_condition: ', res_conditions)

# 使用字典给filter_by方法传参
condition_dict = dict()
condition_dict['name'] = '李四'
condition_dict['id'] = 1755
res_condition_dict = db.session.query(UserInfo).filter_by(**condition_dict).all()
print('res_condition_dict: ', res_condition_dict)

# 过滤和分页查询
@base.route('/build/construction/page', methods=['GET'])
def TbConstructionSit_page():
    area = request.args.get("area")
    name = request.args.get("name")
    contact = request.args.get("contact")
    phone = request.args.get("phone")
    # 查询的列表
    filters = []
    # '' none
    if name:
        filters.append(TbConstructionSite.name.like('%' + name + '%'))
    if area:
        filters.append(TbConstructionSite.area.like('%' + area + '%'))
    if contact:
        filters.append(TbConstructionSite.contact.like('%' + contact + '%'))
    if phone:
        filters.append(TbConstructionSite.phone.like('%' + phone + '%'))
    # 从第pageNum页开始，每页显示pageSize条
    pageNum = request.args.get("pageNum", default=1, type=int)
    pageSize = request.args.get("pageSize", default=10, type=int)
    # 返回分页的对象
    pagination = TbConstructionSite.query.filter(*filters).paginate(page=pageNum, per_page=pageSize, error_out=False)
    # 查询5的数据
    ite = pagination.items
    # 所有的数据
    total = pagination.total
    # return json.dumps([i.to_dict() for i in ite],ensure_ascii=False)
    return json.dumps({'total': total, 'data': [i.to_dict() for i in ite]}, ensure_ascii=False)
